# 这一部分内容主要是桩号反算结果精度评定
# 目前只写了2d, 3d长度与桩号长度的结果差异 -20210729
import matplotlib as plt
from prepocessing.shp_to_pg import *
from settings import *
warnings.filterwarnings('ignore')

# 支持中文
plt.rcParams['font.sans-serif'] = ['SimHei']  # 用来正常显示中文标签
plt.rcParams['axes.unicode_minus'] = False  # 用来正常显示负号


class stake_AccuracyAsses():
    def len_2d_sta(self, table_name):
        """
        2d长度与桩号长度的差异
        Args:
            table_name: 要统计的表， 必备字段（ldbm text, qdzh float, zdzh float, geom linestring）
                                    可选字段（length_2d float, length_zh float）

        Returns:新增字段 abs_2d_zh
        """
        cur = conn.cursor()
        # 字段检查
        try:
            sql = f"select length_2d, length_zh from {table_name};"
            cur.execute(sql)
        except:
            conn.commit()
            sql = f"""
                    alter table {table_name} add column if not exists length_2d float ;
                    alter table {table_name} add column if not exists length_zh float ;
                    update {table_name} set length_2d = st_length(geom);
                    update {table_name} set length_zh = (zdzh - qdzh)*1000;"""
            cur.execute(sql)

        sql = f"""
                alter table {table_name} add column if not exists abs_2d_zh float;
                update {table_name} set abs_2d_zh = ABS(length_2d - length_zh);
                """
        cur.execute(sql)

        df = pd.read_sql(f"select abs_2d_zh as diff from {table_name} where st_intersects(geom, (select geom from study_area))", conn).values.flatten()
        ave_diff = np.average(df)

        df = pd.read_sql(f"select 1000*abs_2d_zh/length_2d as diff from {table_name} where st_intersects(geom, (select geom from study_area))",
                         conn)
        ave_diff_per = np.average(df)
        per = (df[df['diff'] < 50]).shape[0] / df['diff'].shape[0]

        # print(table_name, '平均差异', ave_diff)
        # print(table_name, '平均每百米差异', ave_diff_per)
        print(table_name, '百米差异小于5米占比', per)

    def len_3d_sta(self, table_name):
        """
        3d长度与桩号长度的差异
        Args:
            table_name: 要统计的表， 必备字段（ldbm text, qdzh float, zdzh float, length_3d float, geom linestring）
                                    可选字段（ length_zh float）

        Returns:新增字段 abs_3d_zh
        """
        cur = conn.cursor()
        # 字段检查
        try:
            sql = f"select length_zh from {table_name};"
            cur.execute(sql)
        except:
            conn.commit()
            sql = f"""
                    alter table {table_name} add column if not exists length_zh float ;
                    update {table_name} set length_zh = (zdzh - qdzh)*1000;"""
            cur.execute(sql)

        sql = f"""
                alter table {table_name} add column if not exists abs_3d_zh float;
                update {table_name} set abs_3d_zh = ABS(length_3d - length_zh);
                """
        cur.execute(sql)

        df = pd.read_sql(f"select abs_3d_zh from {table_name}", conn).values.flatten()
        ave_diff = np.average(df)
        max_diff = np.max(df)
        min_diff = np.min(df)
        return ave_diff, max_diff, min_diff

